﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ProcentObject
    {
        public const string sql = @"
SELECT 
	DictionaryPartners.Name as Партнер,
	aa.Summa as Сумма,
	DictionaryOperTypes.Name as ТипРабот,
	DictionaryCurrency.ShortName as Валюта,
	(aa.Summa/SumAll.Summa2)*100 as Процент
FROM (
		select w.Customer, 
			Sum(COALESCE(w.PriceOfSales,0)) as Summa, 
			w.OperType,
			w.CurrencySales
		from Work w
		Group by Customer, OperType, CurrencySales
) as aa

INNER JOIN
	(select Customer, Sum(COALESCE(w2.PriceOfSales,0)) as Summa2 
	from Work w2
	--where PriceOfSales != 0 
	group by Customer) as SumAll on SumAll.Customer = aa.Customer

INNER JOIN DictionaryPartners on DictionaryPartners.Id = aa.Customer
INNER JOIN DictionaryCurrency on DictionaryCurrency.Id = aa.CurrencySales
INNER JOIN DictionaryOperTypes on DictionaryOperTypes.Id = aa.OperType

ORDER BY Партнер, Сумма, ТипРабот, Валюта, Процент
";

        public string Партнер { get; set; }
        public decimal? Сумма { get; set; }
        public string ТипРабот { get; set; }
        public string Валюта { get; set; }
        public decimal? Процент { get; set; }
    }
}
